import sqlite3, logging, os

ch = logging.StreamHandler()
formatter = logging.Formatter('Line%(lineno)d-%(levelname)s: - %(message)s')
ch.setFormatter(formatter)
logger = logging.getLogger('debug')
logger.setLevel(logging.INFO)
logger.addHandler(ch)

TILE_META = ['profile', 'name', 'format', 'bounds', 'zooms', 'version', 'type', 'description']

VECTOR_META = ['profile', 'name', 'format', 'bounds', 'version', 'type', 'description']

sample_tile_meta = {'name': 'sewers',
              'type': 'overlay',
              'version': '1.0',
              'description': 'MKII DEMO',
              'format': 'png',
              'bounds': '',
              'zooms': '0,20',
              'profile': 'global-mercator'}


sample_vector_meta = {'name': 'sewers',
              'type': 'vector',
              'version': '1.0',
              'description': 'MKII DEMO',
              'format': 'geojson',
              'bounds': '',
              '<layer>_template': '<tpl></tp>', #the template apply to the geojson file
              'zooms': '0,20',
              'profile': 'global-mercator'}

def add_meta(src, values, valid_meta=TILE_META):
    '''
    Adding meta data into a database
    https://github.com/mapbox/mbtiles-spec/blob/master/1.1/spec.md
    
    '''
    con = sqlite3.connect(src)
    c = con.cursor()
    sql = "SELECT name FROM sqlite_master WHERE type='table' and name = 'metadata'"
    if c.execute(sql).fetchone():
        logger.info('Table already exist.')
    else:
        logger.info('New Table created')
    sql = 'create table if not exists metadata (name text, value text);'
    c.execute(sql)
    con.commit()
    for k, v in values.items():
        if k.lower() in valid_meta:
            sql = 'select value, rowid from metadata where name=?'
            r = c.execute(sql, [k]).fetchone()
            if r:
                if r[0]==v:
                    logger.info('Same as existing record: %s=%s' % (k, v))
                else:
                    logger.warning('Overwrite existing value. %s: %s=>%s' % (k, r[0], v))
                    sql = 'update metadata set value=? where rowid=?'
                    c.execute(sql, [v, r[1]])
                    con.commit()
            else:
                logger.info('inserting new value: %s=%s' % (k,v))
                sql = 'insert into metadata values (?, ?)'
                c.execute(sql, [k, v])
                con.commit()
        else:
            logger.warning('Not a valid meta attribute: %s\n Skipped.' % k)

def add_tile_meta(src, values):
    add_meta(src, values, TILE_META)


def add_vector_meta(src, values):
    add_meta(src, values, VECTOR_META)
def test():
    path = 'test_add_meta.sqlite'
    if os.path.exists(path):
        os.remove(path)
    sqlite3.connect(path)
    values = {'name': 'sewers',
              'type': 'overlay',
              'version': '1.0',
              'description': 'MKII DEMO',
              'format': 'png',
              'bounds': '',
              'zooms': '0,20',
              'profile': 'global-mercator'}
    
    logger.info('Testing add new meta')
    add_meta(path, values)
    logger.info('Testing adding same value')
    add_meta(path, values)
    logger.info('Testing adding different value')
    values['type'] = 'baselayer'
    values['haha'] = 'ok'
    add_meta(path, values)


vector_meta = {'name': 'sewers',
              'type': 'vector',
              'version': '1.0',
              'description': 'MKII DEMO',
              'format': 'geojson',
              'bounds': '',
              'profile': 'global-mercator'}

#add_vector_meta('../data/vector.sqlite', vector_meta)
tile_meta = {'name': 'sewers',
              'type': 'overlay',
              'version': '1.0',
              'description': 'MKII DEMO',
              'format': 'png',
              'bounds': '',
              'zooms': '0,20',
              'profile': 'global-mercator'}
#add_tile_meta('../data/tile.sqlite', tile_meta)
tile_meta = {'name': 'OSM',
              'type': 'baselayer',
              'version': '1.0',
              'description': 'OSM',
              'format': 'png',
              'bounds': '',
              'zooms': '0,20',
              'profile': 'global-mercator'}
add_tile_meta('../data/osm.sqlite', tile_meta)